import javax.naming.InitialContext;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.*;
import javax.sql.*;
import java.io.*;
import javax.naming.*;

@WebServlet("/servlet")
public class Servlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) {

        doPost(req,resp);
    }
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) {
        String create=
                "CREATE TABLE IF NOT EXISTS `Student` (" +
                        "  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT," +
                        "  `CName` varchar(100) NOT NULL," +
                        "  `Score` int(11) NOT NULL," +
                        "  PRIMARY KEY (`Id`)" +
                        ");";
        String Insert=
                "insert into `Student` (`CName`,`Score`) values ('语文', 86);" +
                        "insert into `Student` (`CName`,`Score`) values ('数学', 100);"+
                        "insert into `Student` (`CName`,`Score`) values ('英语', 97);";
        String selet=
                "select * from Student";

        InitialContext context = null;
        resp.setContentType("text/html");
        resp.setCharacterEncoding("UTF-8");
        try {
            context = new InitialContext();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        DataSource dataSource=null;
        try {
            dataSource =
                    (DataSource) context.lookup("jdbc/DSTest");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn=dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(create);
            stmt.executeUpdate(Insert);
            rset=stmt.executeQuery(selet);
            if (rset.next()) {
                do {
                    resp.getWriter().println(rset.getString("Id")+"-"+     rset.getString("CName")+"-"+rset.getString("Score")+"<br>");
                    //System.out.println(rset.getString("Id")+"-"+     rset.getString("CName")+"-"+rset.getString("Score"));
                } while (rset.next());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
